﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Globalization;
using System.Text;
using System.Reflection;

namespace Adams.Web.Data
{
    public static class DB
    {

        #region Private Functions

        private static string GetconnectionString()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }
        private static string GetconnectionString(string connectionName)
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
        }
        private static void CloseConnection(SqlConnection Connection)
        {
            if (Connection != null)
            {
                Connection.Close();
                Connection.Dispose();
            }
        }
        private static void DetailParameter(SqlParameter parameter, ref StringBuilder sb)
        {
            if (parameter.SqlDbType != SqlDbType.VarBinary)
            {
                sb.Append(parameter.ParameterName + ":" + parameter.Value.ToString() + ",");
            }
            else
            {
                sb.Append(parameter.ParameterName + ":binary,");
            }
        }

        #endregion

        #region CreateConnection

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static SqlConnection CreateConnection()
        {
            return _CreateConnection(null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static SqlConnection CreateConnection(string connectionString)
        {
            return _CreateConnection(connectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private static SqlConnection _CreateConnection(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                connectionString = GetconnectionString();
            else
                connectionString = GetconnectionString(connectionString);

            SqlConnection Conn = new SqlConnection(connectionString);

            Conn.Open();
            return Conn;
        }

        #endregion

        #region CreateTransaction

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static SqlTransaction CreateTransaction()
        {
            return _CreateTransaction(null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static SqlTransaction CreateTransaction(string connectionString)
        {
            return _CreateTransaction(connectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private static SqlTransaction _CreateTransaction(string connectionString)
        {
            SqlConnection Conn = _CreateConnection(connectionString);
            SqlTransaction Trans = Conn.BeginTransaction();
            return Trans;
        }

        #endregion

        #region Run

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        public static void Run(string sql)
        {
            _Run(sql, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        public static void Run(string sql, CommandType cmdType)
        {
            _Run(sql, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        public static void Run(string sql, SqlTransaction transaction)
        {
            _Run(sql, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        public static void Run(string sql, CommandType cmdType, SqlTransaction transaction)
        {
            _Run(sql, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, params SqlParameter[] commandParameters)
        {
            _Run(sql, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            _Run(sql, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            _Run(sql, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            _Run(sql, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        public static void Run(string sql, string connectionString)
        {
            _Run(sql, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        public static void Run(string sql, CommandType cmdType, string connectionString)
        {
            _Run(sql, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        public static void Run(string sql, string connectionString, SqlTransaction transaction)
        {
            _Run(sql, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        public static void Run(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            _Run(sql, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, string connectionString, params SqlParameter[] commandParameters)
        {
            _Run(sql, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            _Run(sql, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            _Run(sql, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        public static void Run(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            _Run(sql, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        private static void _Run(string sql, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlConnection Conn = null;
            SqlCommand Cmd = new SqlCommand();
            try
            {
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    Cmd.Transaction = transaction;
                }

                Cmd.Connection = Conn;
                Cmd.CommandType = cmdType;
                Cmd.CommandText = sql;
                Cmd.CommandTimeout = int.MaxValue;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        Cmd.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {
                if (Cmd != null)
                    Cmd.Dispose();
                if (transaction == null)
                    CloseConnection(Conn);
            }
            if (Ex != null)
                throw Ex;
        }

        #endregion

        #region RunDataReader

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql)
        {
            return _RunDataReader(sql, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType)
        {
            return _RunDataReader(sql, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, SqlTransaction transaction)
        {
            return _RunDataReader(sql, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunDataReader(sql, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, string connectionString)
        {
            return _RunDataReader(sql, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, string connectionString)
        {
            return _RunDataReader(sql, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, string connectionString, SqlTransaction transaction)
        {
            return _RunDataReader(sql, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunDataReader(sql, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static SqlDataReader RunDataReader(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataReader(sql, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static SqlDataReader _RunDataReader(string sql, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlDataReader Reader = null;
            SqlConnection Conn = null;
            SqlCommand Cmd = new SqlCommand();
            try
            {
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    Cmd.Transaction = transaction;
                }

                Cmd.Connection = Conn;
                Cmd.CommandText = sql;
                Cmd.CommandTimeout = int.MaxValue;

                if (cmdType != CommandType.Text)
                    Cmd.CommandType = cmdType;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        Cmd.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                if (transaction == null)
                    Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection);
                else
                    Reader = Cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {

            }
            if (Ex == null)
                return Reader;
            else
            {
                if (Reader != null)
                    Reader.Dispose();
                throw Ex;
            }
        }

        #endregion

        #region RunDataSet

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql)
        {
            return _RunDataSet(sql, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType)
        {
            return _RunDataSet(sql, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, SqlTransaction transaction)
        {
            return _RunDataSet(sql, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunDataSet(sql, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, string connectionString)
        {
            return _RunDataSet(sql, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, string connectionString)
        {
            return _RunDataSet(sql, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, string connectionString, SqlTransaction transaction)
        {
            return _RunDataSet(sql, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunDataSet(sql, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataSet RunDataSet(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataSet(sql, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static DataSet _RunDataSet(string sql, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlConnection Conn = null;
            DataSet DS = new DataSet();
            DS.Locale = CultureInfo.InvariantCulture;
            SqlDataAdapter DA = new SqlDataAdapter();
            try
            {
                DA.SelectCommand = new SqlCommand();
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    DA.SelectCommand.Transaction = transaction;
                }

                DA.SelectCommand.Connection = Conn;
                DA.SelectCommand.CommandText = sql;
                DA.SelectCommand.CommandTimeout = int.MaxValue;

                if (cmdType != CommandType.Text)
                    DA.SelectCommand.CommandType = cmdType;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        DA.SelectCommand.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                DA.Fill(DS);
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {
                if (DA != null)
                    DA.Dispose();
                if (transaction == null)
                    CloseConnection(Conn);
            }
            if (Ex == null)
                return DS;
            else
            {
                if (DS != null)
                    DS.Dispose();
                throw Ex;
            }
        }

        #endregion

        #region RunDataTable

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql)
        {
            return _RunDataTable(sql, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType)
        {
            return _RunDataTable(sql, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, SqlTransaction transaction)
        {
            return _RunDataTable(sql, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunDataTable(sql, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, string connectionString)
        {
            return _RunDataTable(sql, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, string connectionString)
        {
            return _RunDataTable(sql, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, string connectionString, SqlTransaction transaction)
        {
            return _RunDataTable(sql, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunDataTable(sql, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static DataTable RunDataTable(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunDataTable(sql, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static DataTable _RunDataTable(string sql, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlConnection Conn = null;
            DataSet DS = new DataSet();
            DS.Locale = CultureInfo.InvariantCulture;
            SqlDataAdapter DA = new SqlDataAdapter();
            try
            {
                DA.SelectCommand = new SqlCommand();
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    DA.SelectCommand.Transaction = transaction;
                }

                DA.SelectCommand.Connection = Conn;
                DA.SelectCommand.CommandText = sql;
                DA.SelectCommand.CommandTimeout = int.MaxValue;

                if (cmdType != CommandType.Text)
                    DA.SelectCommand.CommandType = cmdType;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        DA.SelectCommand.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                DA.Fill(DS);
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {
                if (DA != null)
                    DA.Dispose();
                if (transaction == null)
                    CloseConnection(Conn);
            }
            if (Ex == null)
                return DS.Tables[0];
            else
            {
                if (DS != null)
                    DS.Dispose();
                throw Ex;
            }
        }

        #endregion

        #region RunScalar

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object RunScalar(string sql)
        {
            return _RunScalar(sql, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType)
        {
            return _RunScalar(sql, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, SqlTransaction transaction)
        {
            return _RunScalar(sql, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunScalar(sql, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, string connectionString)
        {
            return _RunScalar(sql, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, string connectionString)
        {
            return _RunScalar(sql, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, string connectionString, SqlTransaction transaction)
        {
            return _RunScalar(sql, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunScalar(sql, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunScalar(string sql, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunScalar(sql, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static object _RunScalar(string sql, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlConnection Conn = null;
            SqlCommand Cmd = new SqlCommand();
            object retVal = null;
            try
            {
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    Cmd.Transaction = transaction;
                }

                Cmd.Connection = Conn;
                Cmd.CommandText = sql;
                Cmd.CommandTimeout = int.MaxValue;

                if (cmdType != CommandType.Text)
                    Cmd.CommandType = cmdType;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        Cmd.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                retVal = Cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {
                if (Cmd != null)
                    Cmd.Dispose();
                if (transaction == null)
                    CloseConnection(Conn);
            }
            if (Ex == null)
                return retVal;
            else
            {
                throw Ex;
            }
        }

        #endregion

        #region RunOutput

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, string connectionString)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, string connectionString)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, string connectionString, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, 0, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, null, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, string connectionString)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, CommandType.Text, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, string connectionString)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, cmdType, null, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, string connectionString, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, CommandType.Text, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, string connectionString, SqlTransaction transaction)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, cmdType, transaction, null);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, CommandType.Text, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, string connectionString, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, cmdType, null, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, CommandType.Text, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="cmdType"></param>
        /// <param name="connectionString"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, CommandType cmdType, string connectionString, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            return _RunOutput(sql, outputParameterName, outputParameterType, outputParameterSize, connectionString, cmdType, transaction, commandParameters);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="outputParameterName"></param>
        /// <param name="outputParameterType"></param>
        /// <param name="outputParameterSize"></param>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="transaction"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        private static object _RunOutput(string sql, string outputParameterName, SqlDbType outputParameterType, int outputParameterSize, string connectionString, CommandType cmdType, SqlTransaction transaction, params SqlParameter[] commandParameters)
        {
            Exception Ex = null;
            SqlConnection Conn = null;
            SqlCommand Cmd = new SqlCommand();
            object retVal = null;
            try
            {
                if (transaction == null)
                {
                    Conn = _CreateConnection(connectionString);
                }
                else
                {
                    Conn = transaction.Connection;
                    Cmd.Transaction = transaction;
                }

                Cmd.Connection = Conn;
                Cmd.CommandText = sql;
                Cmd.CommandTimeout = int.MaxValue;

                if (cmdType != CommandType.Text)
                    Cmd.CommandType = cmdType;

                var details = new StringBuilder();
                if (commandParameters != null)
                {
                    foreach (SqlParameter P in commandParameters)
                    {
                        Cmd.Parameters.Add(P);
                        DetailParameter(P, ref details);
                    }
                }

                SqlParameter OP = new SqlParameter();
                OP.ParameterName = outputParameterName;
                OP.Direction = ParameterDirection.Output;
                OP.SqlDbType = outputParameterType;
                if (outputParameterSize != 0)
                    OP.Size = outputParameterSize;
                Cmd.Parameters.Add(OP);

                Cmd.ExecuteNonQuery();
                retVal = Cmd.Parameters[outputParameterName].Value;
            }
            catch (Exception ex)
            {
                Ex = ex;
            }
            finally
            {
                if (Cmd != null)
                    Cmd.Dispose();
                if (transaction == null)
                    CloseConnection(Conn);
            }
            if (Ex == null)
                return retVal;
            else
            {
                throw Ex;
            }
        }

        #endregion

        #region CreateOutputParameter

        public static SqlParameter CreateOutputParameter(string parameterName, SqlDbType columnType)
        {
            return _CreateOutputParameter(parameterName, columnType, 0);
        }

        public static SqlParameter CreateOutputParameter(string parameterName, SqlDbType columnType, int size)
        {
            return _CreateOutputParameter(parameterName, columnType, size);
        }

        private static SqlParameter _CreateOutputParameter(string parameterName, SqlDbType columnType, int size)
        {
            SqlParameter Param = new SqlParameter();
            Param.Direction = ParameterDirection.Output;
            Param.ParameterName = parameterName;
            if (columnType != SqlDbType.Variant)
                Param.SqlDbType = columnType;
            if (size != 0)
                Param.Size = size;

            return Param;
        }

        #endregion

        #region CreateParameter

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static SqlParameter CreateParameter(string parameterName, object value)
        {
            return _CreateParameter(parameterName, value, SqlDbType.Variant, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="columnType"></param>
        /// <returns></returns>
        public static SqlParameter CreateParameter(string parameterName, object value, SqlDbType columnType)
        {
            return _CreateParameter(parameterName, value, columnType, 0);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="columnType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public static SqlParameter CreateParameter(string parameterName, object value, SqlDbType columnType, int size)
        {
            return _CreateParameter(parameterName, value, columnType, size);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="columnType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        private static SqlParameter _CreateParameter(string parameterName, object value, SqlDbType columnType, int size)
        {
            SqlParameter Param = new SqlParameter();
            Param.Direction = ParameterDirection.Input;
            Param.ParameterName = parameterName;
            if (columnType != SqlDbType.Variant)
                Param.SqlDbType = columnType;
            if (size != 0)
                Param.Size = size;
            Param.Value = value;

            return Param;
        }

        #endregion

        #region Paging Query Builder

        /// <summary>
        /// 
        /// </summary>
        /// <param name="startRow"></param>
        /// <param name="pageSize"></param>
        /// <param name="fields"></param>
        /// <param name="tables"></param>
        /// <param name="filter"></param>
        /// <param name="group"></param>
        /// <param name="sortExpression"></param>
        /// <param name="idColumn"></param>
        /// <returns></returns>
        public static string BuildPagingQuery(int startRow, int pageSize, string fields, string tables, string filter, string group, string sortExpression, string idColumn)
        {
            return _BuildPagingQuery(startRow, pageSize, fields, tables, filter, group, sortExpression, idColumn);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="startRow"></param>
        /// <param name="pageSize"></param>
        /// <param name="fields"></param>
        /// <param name="tables"></param>
        /// <param name="filter"></param>
        /// <param name="group"></param>
        /// <param name="sortExpression"></param>
        /// <param name="idColumn"></param>
        /// <returns></returns>
        private static string _BuildPagingQuery(int startRow, int pageSize, string fields, string tables, string filter, string group, string sortExpression, string idColumn)
        {
            CultureInfo Invc = CultureInfo.InvariantCulture;
            StringBuilder SB = new StringBuilder();
            string ssortCode = string.Empty;
            string ssortColumn = string.Empty;
            string ssortDirection = string.Empty;
            if (string.IsNullOrEmpty(sortExpression))
            {
                ssortColumn = idColumn;
                ssortDirection = "ASC";
                ssortCode = " ORDER BY " + ssortColumn + " " + ssortDirection;
            }
            else
            {
                ssortCode = " ORDER BY " + sortExpression;
            }

            string spageSize = pageSize.ToString(Invc);
            string sfilterCode;
            if (filter.Length == 0)
            {
                sfilterCode = string.Empty;
            }
            else
            {
                sfilterCode = " WHERE " + filter + " ";
            }
            string sgroupCode;
            if (group.Length == 0)
            {
                sgroupCode = string.Empty;
            }
            else
            {
                sgroupCode = " GROUP BY " + group + " ";
            }

            if (startRow == 0)
            {
                SB.Append("SELECT TOP " + spageSize + " " + fields + " FROM " + tables + sfilterCode + sgroupCode + ssortCode);
            }
            else
            {
                int nEndRecord = startRow + pageSize;
                SB.Append("WITH SQLPaging AS (");
                SB.Append("SELECT ROW_NUMBER() OVER (" + ssortCode + ") AS RecordNumber, ");
                SB.Append(fields + " FROM " + tables + sfilterCode + sgroupCode);
                SB.Append(")SELECT * FROM SQLPaging WHERE RecordNumber BETWEEN " + (startRow + 1).ToString(Invc) + " AND " + nEndRecord.ToString(Invc));
            }

            SB.Append(";SELECT COUNT(*) FROM " + tables + sfilterCode + sgroupCode);

            return SB.ToString();
        }

        #endregion

        #region InitFromReader

        public static T InitFromReader<T>(SqlDataReader dr) where T : new()
        {
            return InitFromReader<T>(dr, null);
        }

        public static T InitFromReader<T>(SqlDataReader dr, params ColMap[] colmaps) where T : new()
        {
            T destObject = new T();

            if (dr == null)
            {
                return default(T);
            }

            if (!dr.HasRows && !dr.IsClosed)
            {
                return default(T);
            }

            Type targetType = destObject.GetType();

            for (int i = 0; i < dr.FieldCount; i++)
            {
                string field = dr.GetName(i);

                PropertyInfo targetObj = targetType.GetProperty(field);
                if (targetObj != null)
                {
                    if (dr.GetValue(i) != DBNull.Value)
                    {
                        targetObj.SetValue(destObject, dr.GetValue(i), null);
                    }
                }
            }

            if (colmaps != null)
            {
                foreach (var colmap in colmaps)
                {
                    PropertyInfo targetObj = targetType.GetProperty(colmap.NameInObject);
                    if (targetObj != null)
                    {
                        if (dr[colmap.NameInDb] != DBNull.Value)
                        {
                            targetObj.SetValue(destObject, dr[colmap.NameInDb], null);
                        }
                    }
                }
            }

            return destObject;
        }

        #endregion
    }

    public class ColMap
    {
        public string NameInDb { get; set; }
        public string NameInObject { get; set; }
    }
}
